package com.hdsx.jxcsxm.tjbb.controller;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.hdsx.jxcsxm.jhcx.server.impl.JhcxServerImpl;
import com.hdsx.jxcsxm.tjbb.bean.*;
import com.hdsx.jxcsxm.tjbb.server.SbzjCgsjhServer;
import com.hdsx.jxcsxm.utile.*;
import com.hdsx.jxcsxm.utile.excel.*;
import com.hdsx.jxcsxm.utile.excel.ExcelExportUtil;
import com.hdsx.jxcsxm.zjbf.bean.XmZjbf;
import net.sf.json.JSONArray;
import net.sf.json.JsonConfig;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.struts2.ServletActionContext;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.hdsx.jxcsxm.tjbb.server.SbzjServer;
import com.hdsx.jxcsxm.tjbb.server.TjbbServer;
import com.hdsx.jxcsxm.xtgl.bean.TreeNode;
import com.hdsx.jxcsxm.xtgl.bean.Xmjbxx;
import com.hdsx.webutil.struts.BaseActionSupport;
import com.opensymphony.xwork2.ModelDriven;



/**
 * Controller层
 * @author qwh
 *
 */
@SuppressWarnings("serial")
@Scope("prototype")
@Controller
public class SbzjController extends BaseActionSupport implements ModelDriven<Excel_list>{
	
	@Resource(name = "sbzjServerImpl")
	private SbzjServer sbzjServer;

	private Xmjbxx xmjbxx=new Xmjbxx();
	private int page=1;
	private int rows=10;
	private Excel_list elist=new Excel_list();

	private File fileupload;
	private String fileuploadFileName;
	SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日");

	public File getFileupload() {
		return fileupload;
	}

	public void setFileupload(File fileupload) {
		this.fileupload = fileupload;
	}

	public String getFileuploadFileName() {
		return fileuploadFileName;
	}

	public void setFileuploadFileName(String fileuploadFileName) {
		this.fileuploadFileName = fileuploadFileName;
	}

	@Override
	public Excel_list getModel() {
		return elist;
	}
	
	public Xmjbxx getXmjbxx() {
		return xmjbxx;
	}

	public void setXmjbxx(Xmjbxx xmjbxx) {
		this.xmjbxx = xmjbxx;
	}
	
	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}

	public int getRows() {
		return rows;
	}

	public void setRows(int rows) {
		this.rows = rows;
	}

	private File uploadGkpf;
	private String uploadGkpfFileName;
	private Plan_upload uploads;

	public Plan_upload getUploads() {
		return uploads;
	}

	public void setUploads(Plan_upload uploads) {
		this.uploads = uploads;
	}

	public String getUploadGkpfFileName() {
		return uploadGkpfFileName;
	}

	public void setUploadGkpfFileName(String uploadGkpfFileName) {
		this.uploadGkpfFileName = uploadGkpfFileName;
	}

	public File getUploadGkpf() {
		return uploadGkpf;
	}

	public void setUploadGkpf(File uploadGkpf) {
		this.uploadGkpf = uploadGkpf;
	}

	public void autoxmmc() throws Exception {
		List<Xmjbxx> l=new ArrayList<Xmjbxx>();
		l=sbzjServer.autoxmmc(xmjbxx.getXmmc().trim());
		
		if(l.size()==0) {
			Xmjbxx x=new Xmjbxx();
			x.setXmmc(xmjbxx.getXmmc());
			l.add(x);
		}
		JsonUtils.write(l, getresponse().getWriter());
	}
	public void autoNoticeName() throws Exception {
		List<Xmjbxx> l=new ArrayList<Xmjbxx>();
		l=sbzjServer.autoNoticeName(xmjbxx.getName().trim());

		if(l.size()==0) {
			Xmjbxx x=new Xmjbxx();
			x.setName(xmjbxx.getName());
			l.add(x);
		}
		JsonUtils.write(l, getresponse().getWriter());
	}

	public void autohm() throws Exception {
		if(xmjbxx.getHm()==null||"".equals(xmjbxx.getHm())||"3".equals(xmjbxx.getHm())) {
			xmjbxx.setHm("%");
		}else {
			
		}
		
		List<Xmjbxx> l=new ArrayList<Xmjbxx>();
		l=sbzjServer.autohm(xmjbxx.getHm().trim());
		
	
		JsonUtils.write(l, getresponse().getWriter());
	}
	
	
	public void queryysqlist() {
		if(xmjbxx.getPage()==0){
			xmjbxx.setPage(page);
		}
		if(xmjbxx.getRows()==0){
			xmjbxx.setRows(rows);
		}
		List<Xmjbxx> list=sbzjServer.queryysqlist(xmjbxx);
		int count=sbzjServer.queryysqlistCount(xmjbxx);
		EasyUIPage<Xmjbxx> e=new EasyUIPage<Xmjbxx>();
		e.setRows(list);
		e.setTotal(count);
		try {
			JsonUtils.write(e, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
	
	public void delsqmx() {
		boolean bl=sbzjServer.delsqmx(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	
	public void checkNameCf() {
		boolean bl=sbzjServer.checkNameCf(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	
	public void sumye() {
		try {
			Xmjbxx bl=sbzjServer.sumye(xmjbxx);
			JsonUtils.write(bl, getresponse().getWriter());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void sumyeByBJID() {
		try {
			Xmjbxx bl=sbzjServer.sumyeByBJID(xmjbxx);
			JsonUtils.write(bl, getresponse().getWriter());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void querysbzjsj() {
		try {
			List<Xmjbxx> bl=sbzjServer.querysbzjsj(xmjbxx);
			JsonUtils.write(bl, getresponse().getWriter());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void insertSbzjsq() {
		boolean bl=sbzjServer.insertSbzjsq(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	public void insertNotice() {
		boolean bl=sbzjServer.insertNotice(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	public void insertDfzczjlr() {
		boolean bl=sbzjServer.insertDfzczjlr(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}

	public void updateSbzjsqMx() {
		boolean bl=sbzjServer.updateSbzjsqMx(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	
	public void updateSbzjsq() {
		boolean bl=sbzjServer.updateSbzjsq(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	
	public void selectsbzjList(){
		xmjbxx.setPage(page);
		xmjbxx.setRows(rows);
		xmjbxx.setXmnf(MyUtil.getQueryTJ(xmjbxx.getXmnf(), "xmnf"));

		List<Xmjbxx> list=sbzjServer.selectsbzjList(xmjbxx);
		int count=sbzjServer.selectsbzjListCount(xmjbxx);
		EasyUIPage<Xmjbxx> e=new EasyUIPage<Xmjbxx>();
		e.setRows(list);
		e.setTotal(count);
		try {
			JsonUtils.write(e, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
	public void selectNoticeList(){
		xmjbxx.setPage(page);
		xmjbxx.setRows(rows);
//		xmjbxx.setXmnf(MyUtil.getQueryTJ(xmjbxx.getXmnf(), "xmnf"));
		if(!xmjbxx.getYf().equals("")){
			xmjbxx.setFbrq(xmjbxx.getNf()+"-"+xmjbxx.getYf());
		}else{
			xmjbxx.setFbrq(xmjbxx.getNf());
		}

		List<Xmjbxx> list=sbzjServer.selectNoticeList(xmjbxx);
		int count=sbzjServer.selectNoticeListCount(xmjbxx);
		EasyUIPage<Xmjbxx> e=new EasyUIPage<Xmjbxx>();
		e.setRows(list);
		e.setTotal(count);
		try {
			JsonUtils.write(e, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
	public void selectDfzczjlrList(){
		xmjbxx.setPage(page);
		xmjbxx.setRows(rows);
		xmjbxx.setXmnf(MyUtil.getQueryTJ(xmjbxx.getXmnf(), "xmnf"));

		List<Xmjbxx> list=sbzjServer.selectDfzczjlrList(xmjbxx);
		int count=sbzjServer.selectDfzczjlrListCount(xmjbxx);
		EasyUIPage<Xmjbxx> e=new EasyUIPage<Xmjbxx>();
		e.setRows(list);
		e.setTotal(count);
		try {
			JsonUtils.write(e, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}

	public void selectZjqkdwdzbList(){
		xmjbxx.setPage(page);
		xmjbxx.setRows(rows);
        xmjbxx.setDwdm(MyUtil.getQueryDwTJ(xmjbxx.getDwdm(), "dwdm"));
        xmjbxx.setJwh(MyUtil.getQueryTJ(xmjbxx.getJwh(), "jwh"));
        xmjbxx.setXmnf(MyUtil.getQueryTJ(xmjbxx.getXmnf(), "xmnf"));
        xmjbxx.setZjlx(MyUtil.getQueryTJ(xmjbxx.getZjlx(), "zjlx"));
//        xmjbxx.setSfqbtxdw(MyUtil.getQueryTJ(xmjbxx.getSfqbtxdw(), "sfqbtxdw"));

		List<Xmjbxx> list=sbzjServer.selectZjqkdwdzbList(xmjbxx);
		int count=sbzjServer.selectZjqkdwdzbListCount(xmjbxx);
		EasyUIPage<Xmjbxx> e=new EasyUIPage<Xmjbxx>();
		e.setRows(list);
		e.setTotal(count);
		try {
			JsonUtils.write(e, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}


	public void exportZjqkdwdzbList(){
		try {
		elist.setDwdm(MyUtil.getQueryDwTJ(elist.getDwdm(), "dwdm"));
		elist.setJwh(MyUtil.getQueryTJ(elist.getJwh(), "jwh"));
		elist.setXmnf(MyUtil.getQueryTJ(elist.getXmnf(), "xmnf"));
		elist.setZjlx(MyUtil.getQueryTJ(elist.getZjlx(), "zjlx"));
//		elist.setSfqbtxdw(MyUtil.getQueryTJ(elist.getSfqbtxdw(), "sfqbtxdw"));

		List<Excel_list> l = (List<Excel_list>) sbzjServer.exportZjqkdwdzbList(elist);
		ExcelData eldata=new ExcelData();//创建一个类
		eldata.setTitleName(" 资金请款与资金到位对账表");//设置第一行
		eldata.setSheetName("sheet1");//设置sheeet名
		eldata.setFileName(" 资金请款与资金到位对账表");//设置文件名

		List<Excel_tilte> et=new ArrayList<Excel_tilte>();//创建一个list存放表头
		et.add(new Excel_tilte("单位名称",1,1,0,0));
		et.add(new Excel_tilte("项目名称",1,1,1,1));
		et.add(new Excel_tilte("局文号",1,1,2,2));
		et.add(new Excel_tilte("项目年份",1,1,3,3));
		et.add(new Excel_tilte("资金类型",1,1,4,4));
		et.add(new Excel_tilte("请款资金",1,1,5,5));
		et.add(new Excel_tilte("到位资金",1,1,6,6));
		eldata.setEl(l);//将实体list放入类中
		eldata.setEt(et);//将表头内容设置到类里面
		HttpServletResponse response= getresponse();//获得一个HttpServletResponse
		Excel_export.excel_export(eldata,response);

		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}

	public void selectsbzjByzhid(){
		try {
			Xmjbxx zh=sbzjServer.selectsbzjByzhid(xmjbxx);
			JsonUtils.write(zh, getresponse().getWriter());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
	
	public void delsq() {
		boolean bl=sbzjServer.delsq(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	public void delNotice() {
		boolean bl=sbzjServer.delNotice(xmjbxx);
		ResponseUtils.write(getresponse(), bl+"");
	}
	/*public void exportsbzj() throws Exception {
		List<Xmjbxx> l= sbzjServer.getSbzjById(xmjbxx);
		if(l.size()>0){
		int x=1;
		if(l.size()%5==0){
			x=l.size()/5;
		}else{
			x=l.size()/5+1;
		}
		HttpServletResponse response=getresponse();
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="+ new String(xmjbxx.getXmmc().getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		// response.addHeader("Content-Disposition", "attachment;filename="+ new String("李钰".getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		OutputStream out = response.getOutputStream();
		File file = new File( this.getClass().getResource("/").getPath()+"/moudle"+x+".xls");
		FileInputStream in = null;
		in = new FileInputStream(file);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		HSSFSheet sheet = wb.getSheet("sheet1"); 
		HSSFRow row=null;
		HSSFCell cell=null;
		double ljje=0;
		double jhje=0;
		double zhljje=0;
		double ljjetotal=0;
		double jhjetotal=0;
		double zhljjetotal=0;
		HSSFRow rowo = sheet.getRow(1);
		HSSFCell celll = rowo.getCell(3);
		Date date = new SimpleDateFormat("yyyy-MM-dd").parse(xmjbxx.getName()); 
		String now = new SimpleDateFormat("yyyy年MM月dd日").format(date);
		celll.setCellValue("申请日期:" + now);
		List<Double> ljjes = new ArrayList<Double>();
		List<Double> jhjes = new ArrayList<Double>();
		List<Double> zhljjes = new ArrayList<Double>();
		for(int i=0;i<l.size();i++) {
			if(i/5<1){
				row = sheet.createRow((int) i + 4);//创建4行
			}else{
				row = sheet.createRow((int) (i/5)*15 + 3 + i%5);//创建4行
			}
			row.setHeightInPoints(31);
			cell = row.createCell((short) 0);
			cell.setCellValue(i+1);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 1);
			cell.setCellValue(l.get(i).getXmmc());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 2);
			cell.setCellValue(l.get(i).getTwh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 3);
			cell.setCellValue(l.get(i).getBcje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4);
			cell.setCellValue(l.get(i).getHm());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 5);
			cell.setCellValue(l.get(i).getKhh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 6);
			cell.setCellValue(l.get(i).getZh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(l.get(i).getZhjhje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			ljje+=Double.parseDouble(l.get(i).getBcje());
			ljjetotal+=Double.parseDouble(l.get(i).getBcje());
			jhje+=Double.parseDouble(l.get(i).getZhjhje());
			jhjetotal+=Double.parseDouble(l.get(i).getZhjhje());
			zhljje+=Double.parseDouble(l.get(i).getName());
			zhljjetotal+=Double.parseDouble(l.get(i).getName());
			cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(l.get(i).getName());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			if(((i+1)%5==0)||i==(l.size()-1)){
				ljjes.add(ljje);ljje=0;
				jhjes.add(jhje);jhje=0;
				zhljjes.add(zhljje);zhljje=0;
			}
		}
		if(l.size()%5==0){
			for(int i=0;i<(l.size()/5);i++) {
				row = sheet.createRow((int) i*15+9);
				row.setHeightInPoints(31);
				cell = row.createCell((short) 0); cell.setCellValue("本页小计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
				cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 3);
				cell.setCellValue(ljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 7);
				cell.setCellValue(jhjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 8);
				cell.setCellValue(zhljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			}
		}else{
			for(int i=0;i<(l.size()/5+1);i++) {
				row = sheet.createRow((int) i*15+9);
				row.setHeightInPoints(31);
				cell = row.createCell((short) 0); cell.setCellValue("本页小计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
				cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 3);
				cell.setCellValue(ljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 7);
				cell.setCellValue(jhjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 8);
				cell.setCellValue(zhljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			}
		}
		if((l.size()-1)/5>0){
			row = sheet.createRow((int) ((l.size()-1)/5)*15+10);
			row.setHeightInPoints(31);
			cell = row.createCell((short) 3);
			cell.setCellValue(ljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(jhjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(zhljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 0); cell.setCellValue("总计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
			cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
		}
		wb.write(out);
 		out.close();
	}else{
		ResponseUtils.write(getresponse(), "该日期无申请数据，请重新选择日期！");
	}
	};
	*/
	/*public void exportsbzj() throws Exception {
		List<Xmjbxx> l= sbzjServer.getSbzjById(xmjbxx);
		if(l.size()>0){
		int x=1;
		if((l.size()+1)%6==0){
			x=(l.size()+1)/6;
		}else{
			x=(l.size()+1)/6+1;
		}
		HttpServletResponse response=getresponse();
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="+ new String(xmjbxx.getXmmc().getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		// response.addHeader("Content-Disposition", "attachment;filename="+ new String("李钰".getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		OutputStream out = response.getOutputStream();
		File file = new File( this.getClass().getResource("/").getPath()+"/moudle"+x+".xls");
		FileInputStream in = null;
		in = new FileInputStream(file);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		HSSFSheet sheet = wb.getSheet("sheet1"); 
		HSSFRow row=null;
		HSSFCell cell=null;
		double ljje=0;
		double jhje=0;
		double zhljje=0;
		double ljjetotal=0;
		double jhjetotal=0;
		double zhljjetotal=0;
		HSSFRow rowo = sheet.getRow(1);
		HSSFCell celll = rowo.getCell(3);
		Date date = new SimpleDateFormat("yyyy-MM-dd").parse(xmjbxx.getName()); 
		String now = new SimpleDateFormat("yyyy年MM月dd日").format(date);
		celll.setCellValue("申请日期:" + now);
		List<Double> ljjes = new ArrayList<Double>();
		List<Double> jhjes = new ArrayList<Double>();
		List<Double> zhljjes = new ArrayList<Double>();
		for(int i=0;i<l.size();i++) {
			if(i<5){
				row = sheet.createRow((int) i + 4);//创建行
			}else{
				row = sheet.createRow((int) ((i+1)/6)*15 + 3 + (i+1)%6);//创建行
			}
			row.setHeightInPoints(31);
			cell = row.createCell((short) 0);
			cell.setCellValue(i+1);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 1);
			cell.setCellValue(l.get(i).getXmmc());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 2);
			cell.setCellValue(l.get(i).getTwh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 3);
			cell.setCellValue(l.get(i).getBcje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4);
			cell.setCellValue(l.get(i).getHm());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 5);
			cell.setCellValue(l.get(i).getKhh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 6);
			cell.setCellValue(l.get(i).getZh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(l.get(i).getZhjhje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			ljje+=Double.parseDouble(l.get(i).getBcje());
			ljjetotal+=Double.parseDouble(l.get(i).getBcje());
			jhje+=Double.parseDouble(l.get(i).getZhjhje());
			jhjetotal+=Double.parseDouble(l.get(i).getZhjhje());
			zhljje+=Double.parseDouble(l.get(i).getName());
			zhljjetotal+=Double.parseDouble(l.get(i).getName());
			cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(l.get(i).getName());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			if(i<5){
				if( i==4 || i==(l.size()-1)){
					ljjes.add(ljje);ljje=0;
					jhjes.add(jhje);jhje=0;
					zhljjes.add(zhljje);zhljje=0;
				}
			}else{
				if( ((i+1-5)%6==0) ||i==(l.size()-1)){
					ljjes.add(ljje);ljje=0;
					jhjes.add(jhje);jhje=0;
					zhljjes.add(zhljje);zhljje=0;
				}
			}
			
		}
		if((l.size()+1)%6==0){
			for(int i=0;i<(l.size()+1)/6;i++) {
				row = sheet.createRow((int) i*15+9);
				row.setHeightInPoints(31);
				cell = row.createCell((short) 0); cell.setCellValue("本页小计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
				cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 3);
				cell.setCellValue(ljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 7);
				cell.setCellValue(jhjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 8);
				cell.setCellValue(zhljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			}
		}else{
			for(int i=0;i<(l.size()+1)/6+1;i++) {
				row = sheet.createRow((int) i*15+9);
				row.setHeightInPoints(31);
				cell = row.createCell((short) 0); cell.setCellValue("本页小计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
				cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 3);
				cell.setCellValue(ljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 7);
				cell.setCellValue(jhjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 8);
				cell.setCellValue(zhljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			}
		}
		if((l.size()-1)/5>0){
			if((l.size()+1)%6==0){
				row = sheet.createRow((int) ((l.size()+1)/6-1)*15+10);
			}else{
				row = sheet.createRow((int) ((l.size()+1)/6)*15+10);
			}
			
			row.setHeightInPoints(31);
			cell = row.createCell((short) 3);
			cell.setCellValue(ljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(jhjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(zhljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 0); cell.setCellValue("总计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
			cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
		}
		wb.write(out);
 		out.close();
	}else{
		ResponseUtils.write(getresponse(), "该日期无申请数据，请重新选择日期！");
	}
	};*/
	
	public void exportsbzj() throws Exception {
		List<Xmjbxx> l= sbzjServer.getSbzjById(xmjbxx);
		if(l.size()>0){
		int x=1;
		
			x=(l.size()-1)/6+1;
		
		HttpServletResponse response=getresponse();
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="+ new String(xmjbxx.getXmmc().getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		// response.addHeader("Content-Disposition", "attachment;filename="+ new String("李钰".getBytes("gb2312"), "ISO-8859-1")+ ".xls");
		OutputStream out = response.getOutputStream();
		File file = new File( this.getClass().getResource("/").getPath()+"/moudle"+x+".xls");
		FileInputStream in = null;
		in = new FileInputStream(file);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		HSSFSheet sheet = wb.getSheet("sheet1"); 
		HSSFRow row=null;
		HSSFCell cell=null;
		double ljje=0;
		double jhje=0;
		double zhljje=0;
		double ljjetotal=0;
		double jhjetotal=0;
		double zhljjetotal=0;
		/*HSSFRow rowo = sheet.getRow(1);
		HSSFCell celll = rowo.getCell(3);
		Date date = new SimpleDateFormat("yyyy-MM-dd").parse(xmjbxx.getName()); 
		String now = new SimpleDateFormat("yyyy年MM月dd日").format(date);
		celll.setCellValue("申请日期:" + now);*/
		List<Double> ljjes = new ArrayList<Double>();
		List<Double> jhjes = new ArrayList<Double>();
		List<Double> zhljjes = new ArrayList<Double>();
		for(int i=0;i<l.size();i++) {
			
				row = sheet.createRow((int) (i/6)*17 + 4 + (i)%6);//创建行
			
			row.setHeightInPoints(31);
			cell = row.createCell((short) 0);
			cell.setCellValue(i+1);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 1);
			cell.setCellValue(l.get(i).getXmmc());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 2);
			cell.setCellValue(l.get(i).getTwh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 3);
			cell.setCellValue(l.get(i).getBcje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4);
			cell.setCellValue(l.get(i).getHm());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 5);
			cell.setCellValue(l.get(i).getKhh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 6);
			cell.setCellValue(l.get(i).getZh());cell.setCellStyle(ExcelUtil.getHSSFCellStyleLeft(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(l.get(i).getZhjhje());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			ljje+=Double.parseDouble(l.get(i).getBcje());
			ljjetotal+=Double.parseDouble(l.get(i).getBcje());
			jhje+=Double.parseDouble(l.get(i).getZhjhje());
			jhjetotal+=Double.parseDouble(l.get(i).getZhjhje());
			zhljje+=Double.parseDouble(l.get(i).getName());
			zhljjetotal+=Double.parseDouble(l.get(i).getName());
			cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(l.get(i).getName());cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			
			if( (i+1)%6==0 || i==(l.size()-1)){
				ljjes.add(ljje);ljje=0;
				jhjes.add(jhje);jhje=0;
				zhljjes.add(zhljje);zhljje=0;
			}
			
		}
		
			for(int i=0;i<(l.size()-1)/6+1;i++) {
				HSSFRow rowo = sheet.getRow(i*17+1);
				HSSFCell celll = rowo.getCell(3);
				Date date = new SimpleDateFormat("yyyy-MM-dd").parse(xmjbxx.getName()); 
				String now = new SimpleDateFormat("yyyy年MM月dd日").format(date);
				celll.setCellValue("申请日期:" + now);
				    row = sheet.createRow((int) i*17+10);

				row.setHeightInPoints(31);
				cell = row.createCell((short) 0); cell.setCellValue("本页小计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
				cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 3);
				cell.setCellValue(ljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 7);
				cell.setCellValue(jhjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
				cell = row.createCell((short) 8);
				cell.setCellValue(zhljjes.get(i));cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			}
		
		if((l.size()-1)/6>0){
			/*if((l.size()+1)%6==0){
				row = sheet.createRow((int) ((l.size()+1)/6-1)*15+10);
			}else{
				row = sheet.createRow((int) ((l.size()+1)/6)*15+10);
			}*/
			row = sheet.createRow((int) ((l.size())/6)*17+11);
			row.setHeightInPoints(31);
			cell = row.createCell((short) 3);
			cell.setCellValue(ljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 7);
			cell.setCellValue(jhjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 8);
			cell.setCellValue(zhljjetotal);cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 0); cell.setCellValue("总计");cell.setCellStyle(ExcelUtil.getHSSFCellStyleWeght(wb));
			cell = row.createCell((short) 1); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 2); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 4); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));cell = row.createCell((short) 5); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
			cell = row.createCell((short) 6); cell.setCellStyle(ExcelUtil.getHSSFCellStyleCenter(wb));
		}
		wb.write(out);
 		out.close();
	}else{
		ResponseUtils.write(getresponse(), "该日期无申请数据，请重新选择日期！");
	}
	};

	public void uploadGkpf() throws Exception{
		HttpServletResponse response = ServletActionContext.getResponse();
		try {
			String fileurl="D:\\江西综合平台上传文件\\jxcswj\\";
			File file =new File(fileurl);
			if(uploadGkpf!=null){
				String fid= UUID.randomUUID().toString();
				Plan_upload uploads =new Plan_upload(fid,uploadGkpfFileName, xmjbxx.getFiletype(), xmjbxx.getXmbm(),
						"D:\\江西综合平台上传文件/jxcswj/"+uploadGkpfFileName, xmjbxx.getGkpfwh(),fid);
//				CbsjServer cbsjServer =new CbsjServerImpl();
//				uploads.setFid(fid);

				Plan_upload result = sbzjServer.queryFileByWh(uploads);
				if("ggwj".equals(xmjbxx.getFiletype())){
                    if(result==null && sbzjServer.insertFile(uploads)){
                        uploadFile(file,uploadGkpfFileName);
                        response.getWriter().print(uploadGkpfFileName+"上传成功！");
                    }
                }else{
                    if(result==null && sbzjServer.insertFile(uploads) && sbzjServer.insertFileJl(uploads)){
                        uploadFile(file,uploadGkpfFileName);
                        response.getWriter().print(uploadGkpfFileName+"上传成功！");
                    }else{
                        uploads.setFid(result.getId());
                        sbzjServer.insertFileJl(uploads);
                        response.getWriter().print(uploadGkpfFileName+"上传成功！");
                    }
                }


			}
		} catch (Exception e) {
			e.printStackTrace();
			response.getWriter().print(uploadGkpfFileName+"上传失败！");
			throw e;
		}
	}

	private void uploadFile(File file,String fileName) throws FileNotFoundException,IOException {
		if(!file.exists()){
			file.mkdirs();
		}
		InputStream is = new FileInputStream(uploadGkpf);
		File saveFile =new File(file, fileName);
		OutputStream os = new FileOutputStream(saveFile);
		//设置缓存
		byte[] buffer = new byte[1024];
		int length = 0;
		while((length= is.read(buffer))>0){
			os.write(buffer,0,length);
		}
		is.close();
		os.flush();
		os.close();
	}

	public void queryFjByParentId(){
        List<Plan_upload> filelist = new ArrayList<Plan_upload>();
        if("ggwj".equals(uploads.getFiletype())){
            filelist = sbzjServer.queryNoticeFjByParentId(uploads);
        }else{
            filelist = sbzjServer.queryFjByParentId(uploads);
        }

		try {
			JsonUtils.write(filelist, getresponse().getWriter());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 下载文件
	 */
	public void downAbgcFile(){
		try {
			String fileurl = java.net.URLDecoder.decode(uploads.getFileurl(), "UTF-8");
			fileurl = fileurl.replace("amp", "&");
			File file =new File(fileurl);
			HttpServletResponse response = getresponse();
			OutputStream out = response.getOutputStream();
			String fileName = new String(java.net.URLDecoder.decode(uploads.getFilename(), "UTF-8"));
			fileName = fileName.replace("amp", "&");
			response.setContentType("application/x-download;charset=UTF-8");
			response.addHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
			response.setCharacterEncoding("UTF-8");
			byte[] buffer = new byte[1024];
			InputStream is = new FileInputStream(file);
			int length = 0;
			while((length= is.read(buffer))>0){
				out.write(buffer,0,length);
			}
			out.write(buffer);
			out.flush();
			out.close();
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除文件
	 */
	public void deleteFile(){
		try {
			JsonUtils.write(sbzjServer.deleteFile(uploads),getresponse().getWriter());
		}  catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void insertSbzjList(){

		System.out.println("进入方法");
		System.out.println(xmjbxx);
		String fileType=fileuploadFileName.substring(fileuploadFileName.lastIndexOf(".")+1, fileuploadFileName.length());
		HttpServletResponse response = getresponse();

		try {
			if(!"xls".equals(fileType)&&!"xlsx".equals(fileType)){
				response.getWriter().print(fileuploadFileName+"不是系统导出的excel模版文件");
				return ;
			}
			boolean isExcel2003 = true;
			if (fileuploadFileName.matches("^.+\\.(?i)(xlsx)$")) {
				isExcel2003 = false;
			}
			response.setCharacterEncoding("utf-8");
			FileInputStream fs = new FileInputStream(this.fileupload);
			List<Map>[] dataMapArray;
			//System.out.println("isExcel2003============="+isExcel2003);
			if (isExcel2003)
				dataMapArray = ExcelReaderUtils.readExcelContent(0, 24, fs, Object.class);
			else
				dataMapArray = ExcelReaderUtils.readExcelContent2(0, 24, fs, Object.class);
			//dataMapArray = ExcelReader.readExcelContent(0,20,fs,Object.class);
			//System.out.println(dataMapArray[0]);
			List<Map> data = ExcelReader.removeBlankRow(dataMapArray[0]);
			List<Map> bt =new ArrayList<Map>();
			List<Map> gdxm =new ArrayList<Map>();
			List<Map> ncglxm =new ArrayList<Map>();
			List<Map> nr =new ArrayList<Map>();
			List<Map> list =new ArrayList<Map>();
			if(data.size()>=1) {
				for (Map d :
						data) {
					if (!d.get("0").equals("") && d.get("0") != null && d.get("0").toString().replaceAll("\\s*", "").contains("计划录入")) {
						bt.add(d);
					}//设区市	新建	改建	路面改造	养护大中修	灾毁恢复	危桥改造	安防工程	灾害防治	养护工程	示范路
					if ("设区市".equals(d.get("0").toString().replaceAll("\\s*", ""))&& "新建".equals(d.get("1").toString().replaceAll("\\s*", "")) &&
							"改建".equals(d.get("2").toString().replaceAll("\\s*", "")) &&  "路面改造".equals(d.get("3").toString().replaceAll("\\s*", "")) &&
							"灾毁恢复".equals(d.get("5").toString().replaceAll("\\s*", "")) &&  "安防工程".equals(d.get("7").toString().replaceAll("\\s*", "")) &&
							"养护工程".equals(d.get("9").toString().replaceAll("\\s*", "")) &&  "服务区".equals(d.get("11").toString().replaceAll("\\s*", "")) &&
							"道班".equals(d.get("13").toString().replaceAll("\\s*", "")) &&  "平交道口".equals(d.get("15").toString().replaceAll("\\s*", "")) &&
							"连续长陡下坡".equals(d.get("17").toString().replaceAll("\\s*", ""))&& "隧道提质升级".equals(d.get("19").toString().replaceAll("\\s*", "")) &&
							"养护建设项目".equals(d.get("22").toString().replaceAll("\\s*", ""))) {
						gdxm.add(d);
					}
					if ("设区市".equals(d.get("0").toString().replaceAll("\\s*", ""))&& "危桥改造".equals(d.get("1").toString().replaceAll("\\s*", "")) &&
							"安防工程".equals(d.get("2").toString().replaceAll("\\s*", "")) &&  "农村公路建设".equals(d.get("3").toString().replaceAll("\\s*", "")) &&
							"通自然村".equals(d.get("5").toString().replaceAll("\\s*", "")) &&  "民族村".equals(d.get("7").toString().replaceAll("\\s*", "")) &&
							"组组通水泥路".equals(d.get("9").toString().replaceAll("\\s*", "")) &&  "民族村县道升级改造".equals(d.get("11").toString().replaceAll("\\s*", "")) &&
							"养护建设项目".equals(d.get("13").toString().replaceAll("\\s*", ""))) {
						ncglxm.add(d);
					}
					if (!d.get("0").toString().replaceAll("\\s*", "").contains("计划录入") &&
							!"设区市".equals(d.get("0").toString().replaceAll("\\s*", ""))){
						nr.add(d);
					}
				}
				System.out.println("bt=========="+bt);
				System.out.println("gdxm=========="+gdxm);
				System.out.println("ncglxm=========="+ncglxm);
				System.out.println("nr=========="+nr);

				String xmdjserach = xmjbxx.getXmdj();
				if ("国省道".equals(xmdjserach)){
					if (!"国省道资金计划录入".equals(bt.get(0).get("0").toString().replaceAll("\\s*", ""))){
						ResponseUtils.write(getresponse(), "表头不是国省道资金计划录入");
						//response.getWriter().print(fileuploadFileName+"标题不正确");
						return;
					}else {
						if (gdxm.size() > 0 && "设区市".equals(gdxm.get(0).get("0").toString().replaceAll("\\s*", ""))&&
								"路面改造".equals(gdxm.get(0).get("3").toString().replaceAll("\\s*", ""))&&
								"安防工程".equals(gdxm.get(0).get("7").toString().replaceAll("\\s*", ""))) {
							//处理数据
							for (int i = 0; i < nr.size(); i++) {
								//Map<String, String> cgsjhlrMapmx1 = new HashMap<String, String>();
								//项目类型  new2>new1?30:0
								for(int j = 1; j < 23; j++) {
									Map<String, String> cgsjhlrMapmx = new HashMap<String, String>();
									if (!nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", "").isEmpty()) {
										String replaceAll = nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", "");
										boolean matches = replaceAll.matches("^[1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*$");
										if(matches) {
											cgsjhlrMapmx.put("xmlx", gdxm.get(0).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											String uuid = UUID.randomUUID().toString().replaceAll("-", "");
											cgsjhlrMapmx.put("id", uuid);
											cgsjhlrMapmx.put("jhid", xmjbxx.getId());
											cgsjhlrMapmx.put("sqrq", xmjbxx.getSqrq());
											//cgsjhlrMapmx.put("bcje", nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											cgsjhlrMapmx.put("bcje", "0");
											cgsjhlrMapmx.put("zhjhje",nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											cgsjhlrMapmx.put("bz", xmjbxx.getBz());
											String xmdj = xmjbxx.getXmdj();
											String xmlx = nr.get(i).get("0").toString().replaceAll("\\s*", "");
											//System.out.println(nr.get(i).get("0").toString().replaceAll("\\s*", "").contains("鹰潭"));
											boolean contains = nr.get(i).get("0").toString().replaceAll("\\s*", "").contains("鹰潭");
											if (contains) {
												Bkzh autocgshmNcgl = sbzjServer.autosbzjhmNcgl(xmlx);
												if(autocgshmNcgl.getId().toString()!=null) {
													//数据库查询sbzj_bkzh中得id//select * from SBZJ_BKZH where  dwmc like '吉安市'||'%'||'公路'||'%';
													cgsjhlrMapmx.put("zhid", autocgshmNcgl.getId());
												}else {
													response.getWriter().print(fileuploadFileName+"xmdj不能未空");
												}

											}else {
												if ("国省道".equals(xmdj)) {
													Bkzh autocgshmGsd = sbzjServer.autosbzjhmGsd(xmlx);
													//System.out.println("autocgshmGsd.getId().isEmpty()"+autocgshmGsd.getId());
													//System.out.println("autocgshmGsd.getId().isEmpty()"+autocgshmGsd.getId().isEmpty());
													if(!autocgshmGsd.getId().isEmpty()) {
														//System.out.println("autocgshmGsd.getId().isEmpty()00000"+autocgshmGsd.getId());
														//数据库查询sbzj_bkzh中得id//select * from SBZJ_BKZH where  dwmc like '吉安市'||'%'||'公路'||'%';
														cgsjhlrMapmx.put("zhid", autocgshmGsd.getId());
													}else {
														ResponseUtils.write(getresponse(), "项目等级不能为空");
														//response.getWriter().print(fileuploadFileName+"xmdj不能未空");
													}

												}
											}
										}else {
											ResponseUtils.write(getresponse(), "填写内容不是数字格式");
											return;
										}
										list.add(cgsjhlrMapmx);
									}
								}
//
							}
						}else {
							ResponseUtils.write(getresponse(), "execl标题不正确");
							return;
						}
					}
				}
				if ("农村公路".equals(xmdjserach)){
					if (!"农村公路资金计划录入".equals(bt.get(0).get("0").toString().replaceAll("\\s*", ""))){
						ResponseUtils.write(getresponse(), "表头不是农村公路资金计划录入");
						return;
					}else {
						//设区市	危桥改造	安防工程	农村公路建设	养护工程	通自然村	psl贷款	民族村	旅游公路	组组通水泥路	民族村通自然村公路
						// 民族村县道升级改造	美丽生态文明农村路	养护建设项目
						if (ncglxm.size() > 0 && "设区市".equals(ncglxm.get(0).get("0").toString().replaceAll("\\s*", ""))&&
								"农村公路建设".equals(ncglxm.get(0).get("3").toString().replaceAll("\\s*", ""))&&
								"民族村".equals(ncglxm.get(0).get("7").toString().replaceAll("\\s*", "")) &&
								"美丽生态文明农村路".equals(ncglxm.get(0).get("12").toString().replaceAll("\\s*", "")) ) {
							//处理数据
							for (int i = 0; i < nr.size(); i++) {
								//Map<String, String> cgsjhlrMapmx1 = new HashMap<String, String>();
								//项目类型  new2>new1?30:0
								for(int j = 1; j <= 13; j++) {
									Map<String, String> cgsjhlrMapmx = new HashMap<String, String>();
									//!nr.get(i).get(j).toString().replaceAll("\\s*", "").isEmpty()&&nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", "").matches("^[-\\\\\\\\+]?([0-9]+\\\\\\\\.?)?[0-9]+$")
									if (!nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", "").isEmpty()) {
										String replaceAll = nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", "");
										//System.out.println("原始数据"+replaceAll);
										boolean matches = replaceAll.matches("^[1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*$");
										//System.out.println("正则表达式之后"+matches);
										if(matches) {
											cgsjhlrMapmx.put("xmlx", ncglxm.get(0).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											String uuid = UUID.randomUUID().toString().replaceAll("-", "");
											cgsjhlrMapmx.put("id", uuid);
											cgsjhlrMapmx.put("jhid", xmjbxx.getId());
											cgsjhlrMapmx.put("sqrq", xmjbxx.getSqrq());
											//cgsjhlrMapmx.put("bcje", nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											cgsjhlrMapmx.put("bcje", "0");
											cgsjhlrMapmx.put("zhjhje",nr.get(i).get(String.valueOf(j)).toString().replaceAll("\\s*", ""));
											cgsjhlrMapmx.put("bz", xmjbxx.getBz());
											String xmdj = xmjbxx.getXmdj();
											String xmlx = nr.get(i).get("0").toString().replaceAll("\\s*", "");
											//System.out.println(nr.get(i).get("0").toString().replaceAll("\\s*", "").contains("鹰潭"));
											boolean contains = nr.get(i).get("0").toString().replaceAll("\\s*", "").contains("鹰潭");
											if (contains) {
												Bkzh autocgshmNcgl = sbzjServer.autosbzjhmNcgl(xmlx);
												if(autocgshmNcgl.getId().toString()!=null) {
													//数据库查询sbzj_bkzh中得id//select * from SBZJ_BKZH where  dwmc like '吉安市'||'%'||'公路'||'%';
													cgsjhlrMapmx.put("zhid", autocgshmNcgl.getId());
												}else {
													response.getWriter().print(fileuploadFileName+"xmdj不能未空");
												}

											}else {
												if ("农村公路".equals(xmdj)) {
													Bkzh autocgshmNcgl = sbzjServer.autosbzjhmNcgl(xmlx);
													if(!autocgshmNcgl.getId().isEmpty()) {
														//数据库查询sbzj_bkzh中得id//select * from SBZJ_BKZH where  dwmc like '吉安市'||'%'||'公路'||'%';
														cgsjhlrMapmx.put("zhid", autocgshmNcgl.getId());
													}else {
														response.getWriter().print(fileuploadFileName+"xmdj不能未空");
													}

												}
											}
										}else {
											ResponseUtils.write(getresponse(), "填写内容不是数字格式");
											return;
										}
										list.add(cgsjhlrMapmx);
									}
								}
							}
						}else {
							ResponseUtils.write(getresponse(), "execl标题不正确");
							return;
						}
					}
				}

			}else {
				ResponseUtils.write(getresponse(), "execl表内容为空");
				return;
			}
			double total = 0;
			for (int k = 0; k < list.size(); k++) {
				total+=Double.parseDouble(String.valueOf(list.get(k).get("zhjhje")));
			}
			double totaljhje= Double.parseDouble(xmjbxx.getJhje());
			System.out.println(total+"+++++++"+totaljhje);
			System.out.println(total==totaljhje);
			if (total!=totaljhje) {
				ResponseUtils.write(getresponse(), "省补资金记划录入总金额不等于计划金额");
				return;
			}
			System.out.println("list-------------"+"list-------------"+list.size()+list);
			//将数据插入到数据库
			boolean b=sbzjServer.insertSbzjList(xmjbxx,list);
			if(b)
				ResponseUtils.write(getresponse(), b + "");
			else
				ResponseUtils.write(getresponse(), "");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

/*
* 模板导出*/

/*	public void exportjhlrgsdy(){
		HttpServletResponse response = ServletActionContext.getResponse();
		int k = 0;
		try {
			response.setContentType("application/ms-excel;charset=iso8859-1");

			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();

			// 为数据内容设置特点新单元格样式1 自动换行 上下居中
			HSSFCellStyle zidonghuanhang = wb.createCellStyle();
			zidonghuanhang.setWrapText(true);// 设置自动换行
			zidonghuanhang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
			// 设置边框
			zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
			zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);

			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("国省道资金计划录入");
			sheet.setDefaultColumnWidth(15); //统一设置列宽

			// 创建第1行 也就是表头
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
					22)); // 合并列标题
			//创建第二行 标题
//			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
//					0));
			// 创建第1行 也就是表头
			HSSFRow row1 = sheet.createRow(0);
			row1.setHeightInPoints(15);// 设置表头高度
			HSSFRow row2 = sheet.createRow(1);
			row2.setHeightInPoints(15);// 设置表头高度
			HSSFRow row3 = sheet.createRow(2);
			row3.setHeightInPoints(15);// 设置表头高度
			HSSFRow row4 = sheet.createRow(3);
			row4.setHeightInPoints(15);// 设置表头高度

			// 第四步，创建表头单元格样式 以及表头的字体样式
			HSSFCellStyle style = wb.createCellStyle();
			style.setWrapText(true);// 设置自动换行
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
			style.setBottomBorderColor(HSSFColor.BLACK.index);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
			headerFont.setFontHeightInPoints((short) 11); // 设置字体大小
			style.setFont(headerFont); // 为标题样式设置字体样式
			Date date = new Date();
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			String[] columnName1 = {"国省道资金计划录入", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", };
			//设区市	新建	改建	路面改造	养护大中修	灾毁恢复	危桥改造	安防工程	灾害防治	养护工程	示范路	服务区// 养护中心	道班	一级公路	平交道口	桥梁防护	// 连续长陡下坡	三类桥维修	隧道提质升级	不停车监测点	水毁抢修	养护建设项目
			String[] columnName = {"设区市","新建", "改建", "路面改造", "养护大中修", "灾毁恢复", "危桥改造", "安防工程", "灾害防治", "养护工程", "示范路", "服务区", "养护中心", "道班", "一级公路", "平交道口", "桥梁防护", "连续长陡下坡", "三类桥维修", "隧道提质升级", "不停车监测点", "水毁抢修", "养护建设项目"};
			for (int i = 0; i <= 22; i++) {
				HSSFCell cell = row2.createCell(i);
				cell.setCellValue(columnName[i]);
				cell.setCellStyle(style);
			}
			for (int i = 0; i <= 22; i++) {
				HSSFCell cell = row1.createCell(i);
				cell.setCellValue(columnName1[i]);
				cell.setCellStyle(style);
			}
			// 第五步，创建单元格，并设置值

//            sheet.setColumnHidden(8, true);
//            sheet.setColumnHidden(9, true);
			OutputStream os = response.getOutputStream();
			String filename = "国省道资金计划录入";
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("utf8"), "ISO8859-1") + ".xls");//默认Excel名称

			wb.write(os);
			os.flush();
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
*/

	public void exportjhlrgsd() throws  Exception{
			int x=7;
			HttpServletResponse response=getresponse();
			response.setContentType("octets/stream");
			response.addHeader("Content-Disposition", "attachment;filename="+ new String("国省道资金计划录入".getBytes("gb2312"), "ISO-8859-1")+ ".xls");
			OutputStream out = response.getOutputStream();
			File file = new File( this.getClass().getResource("/").getPath()+"/moudle"+x+".xls");
			FileInputStream in = null;
			in = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(in);
			HSSFSheet sheet = wb.getSheet("sheet1");
			wb.write(out);
			out.close();
	}

	//导出模版
	public void exportqklrmbdc() {
		try {
			//设置表头
			ExcelTitleCell[] title = null;
			title = new ExcelTitleCell[10];
			title[0] = new ExcelTitleCell("项目名称", false,
					new ExcelCoordinate(0, (short) 0), null, 20,false);
			title[1] = new ExcelTitleCell("项目类型", false,
					new ExcelCoordinate(0, (short) 1), null, 10,false);
			title[2] = new ExcelTitleCell("单位名称", false,
					new ExcelCoordinate(0, (short) 2), null, 20,false);
			title[3] = new ExcelTitleCell("计划金额", false,
					new ExcelCoordinate(0, (short) 3), null, 20,false);
			title[4] = new ExcelTitleCell("累计请款金额", false,
					new ExcelCoordinate(0, (short) 4), null, 20,false);
			title[5] = new ExcelTitleCell("未请款金额", false,
					new ExcelCoordinate(0, (short) 5), null, 15,false);
			title[6] = new ExcelTitleCell("本次请款金额", false,
					new ExcelCoordinate(0, (short) 6), null, 15,false);
			title[7] = new ExcelTitleCell("申请日期", false,
					new ExcelCoordinate(0, (short) 7), null, 15,false);
			title[8] = new ExcelTitleCell("计划id", true,
					new ExcelCoordinate(0, (short) 8), null, 15,false);
			title[9] = new ExcelTitleCell("账户id", true,
					new ExcelCoordinate(0, (short) 9), null, 15,false);

			//设置列与字段对应
			Map<String, String> attribute = new HashMap<String, String>();
			attribute.put("0", "xmmc");//项目名称
			attribute.put("1", "xmlx");//项目类型
			attribute.put("2", "dwmc");//单位名称
			attribute.put("3", "jhje");//计划金额
			attribute.put("4", "ljqkje");//累计请款金额
			attribute.put("5", "wqkje");//未请款金额
			attribute.put("6", "bcqkje");//本次请款金额
			attribute.put("7", "sqrq");//申请日期
			attribute.put("8", "jhid");//计划id
			attribute.put("9", "zhid");//账户id

			List<Object> excelData = new ArrayList<Object>();

			String titleName = xmjbxx.getMbtitle()+"模版";
			String fileName = xmjbxx.getMbtitle()+"模版";
			List<Xmjbxx> ql = sbzjServer.exportqklrmbdc(xmjbxx);
			excelData.addAll(ql);
			ExcelEntity excel = new ExcelEntity(titleName, title, attribute,excelData);
			ExcelExportUtil.excelWritelock(excel, fileName, getresponse());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void importqklr() {
		String fileType=fileuploadFileName.substring(fileuploadFileName.length()-3, fileuploadFileName.length());
		HttpServletResponse response = getresponse();
		try{
			if(!"xls".equals(fileType)){
				response.getWriter().print(fileuploadFileName+"不是系统导出的excel模版文件");
				return ;
			}
			response.setCharacterEncoding("utf-8");
			FileInputStream fs = new FileInputStream(this.fileupload);
			List<Map>[] dataMapArray;
			try{
				dataMapArray = ExcelReader.readExcelContent(0,20,fs,Object.class);
			}catch(Exception e){
				response.getWriter().print(fileuploadFileName+"数据有误");
				return;
			}
			List<Map> data = ExcelReader.removeBlankRow(dataMapArray[0]);

			if(data.size()>=1) {
				if(data.get(0).get("0").equals("项目名称")&&data.get(0).get("1").equals("项目类型")&&data.get(0).get("2").equals("单位名称")&&data.get(0).get("3").equals("计划金额")&&data.get(0).get("4").equals("累计请款金额")&&data.get(0).get("5").equals("未请款金额")&&data.get(0).get("6").equals("本次请款金额")&&data.get(0).get("7").equals("申请日期"))
					data.remove(0);
				else {
					response.getWriter().print(fileuploadFileName+"数据有误");
					return;
				}
			}else {
				response.getWriter().print(fileuploadFileName+"数据有误");
				return;
			}


			//将数据插入到数据库
			boolean b=sbzjServer.importqklr(data);
			if(b)
				response.getWriter().print(fileuploadFileName+"导入成功");
			else
				response.getWriter().print(fileuploadFileName+"导入失败");
		}catch(Exception e){
			e.printStackTrace();
		}


	}

	public void exportjhlrncgl(){
		OutputStream out = null;
		int x=8;
		HttpServletResponse response=getresponse();
		try {
			out = response.getOutputStream();
			response.setContentType("octets/stream");
			response.addHeader("Content-Disposition", "attachment;filename="+ new String("农村公路资金计划录入".getBytes("gb2312"), "ISO-8859-1")+ ".xls");
			File file = new File( this.getClass().getResource("/").getPath()+"/moudle"+x+".xls");
			FileInputStream in = null;
			in = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(in);
			HSSFSheet sheet = wb.getSheet("sheet1");
			wb.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}


	/*public void exportjhlrncglx(){
		HttpServletResponse response = ServletActionContext.getResponse();
		int k = 0;
		try {
			response.setContentType("application/ms-excel;charset=iso8859-1");

			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();

			// 为数据内容设置特点新单元格样式1 自动换行 上下居中
			HSSFCellStyle zidonghuanhang = wb.createCellStyle();
			zidonghuanhang.setWrapText(true);// 设置自动换行
			zidonghuanhang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
			// 设置边框
			zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
			zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
			zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);

			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("农村公路资金计划录入");
			sheet.setDefaultColumnWidth(20); //统一设置列宽

			// 创建第1行 也就是表头
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
					13)); // 合并列标题
			//创建第二行 标题
//			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
//					0));
			// 创建第1行 也就是表头
			HSSFRow row1 = sheet.createRow(0);
			row1.setHeightInPoints(15);// 设置表头高度
			HSSFRow row2 = sheet.createRow(1);
			row2.setHeightInPoints(15);// 设置表头高度
			HSSFRow row3 = sheet.createRow(2);
			row3.setHeightInPoints(15);// 设置表头高度
			HSSFRow row4 = sheet.createRow(3);
			row4.setHeightInPoints(15);// 设置表头高度

			// 第四步，创建表头单元格样式 以及表头的字体样式
			HSSFCellStyle style = wb.createCellStyle();
			style.setWrapText(true);// 设置自动换行
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
			style.setBottomBorderColor(HSSFColor.BLACK.index);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
			headerFont.setFontHeightInPoints((short) 11); // 设置字体大小
			style.setFont(headerFont); // 为标题样式设置字体样式
			Date date = new Date();
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			String[] columnName1 = {"农村公路资金计划录入", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", };
			//设区市	危桥改造	安防工程	农村公路建设	养护工程	通自然村	psl贷款	民族村	旅游公路	组组通水泥路	民族村通自然村公路	民族村县道升级改造	美丽生态文明农村路	养护建设项目
			String[] columnName = {"设区市","危桥改造", "安防工程", "农村公路建设", "养护工程", "通自然村", "psl贷款", "民族村", "旅游公路", "组组通水泥路", "民族村通自然村公路", "民族村县道升级改造", "美丽生态文明农村路", "养护建设项目"};
			for (int i = 0; i <= 13; i++) {
				HSSFCell cell = row2.createCell(i);
				cell.setCellValue(columnName[i]);
				cell.setCellStyle(style);
			}
			for (int i = 0; i <= 13; i++) {
				HSSFCell cell = row1.createCell(i);
				cell.setCellValue(columnName1[i]);
				cell.setCellStyle(style);
			}
			// 第五步，创建单元格，并设置值

//            sheet.setColumnHidden(8, true);
//            sheet.setColumnHidden(9, true);
			OutputStream os = response.getOutputStream();
			String filename = "农村公路资金计划录入";
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("utf8"), "ISO8859-1") + ".xls");//默认Excel名称

			wb.write(os);
			os.flush();
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}*/
}
